package com.atguigu2.preparedstatement;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;

import org.junit.Test;

import com.atguigu1.connection.ConnectionTest;
import com.atguigu3.util.JDBCUtils;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
/**
 * 除了解决Statement的拼串、SQL注入问题，PreparedStatement还有哪些好处呢？
 * 1.PreparedStatement操作Blob的数据，而Statement做不到。
 * 2.PreparedStatement可以实现更高效的批量操作。
 * @author 石文学
 *
 */
public class PreparedStatementUpdateTest {
	//测试通用增删改操作
	@Test
	public void testCommonUpdate()
	{
		String sql = "delete from customers where id = ?";
		update(sql, 3);
	}
	
	//通用的增删改操作
	@Test
	public void update(String sql,Object ...args)//sql中占位符的个数与可变形参的长度一样
	{
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//1.获取数据库的连接
			conn = JDBCUtils.getConnection();
			//2.预编译sql语句，返回perparedstatement的实例
			ps = (PreparedStatement) conn.prepareStatement(sql);
			//3.填充占位符
			for(int i=0;i<args.length;i++)
			{
				ps.setObject(i+1, args[i]);//小心参数声明错误
			}
			//4.执行
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//5.关闭资源
			JDBCUtils.closeResource(conn, ps);
		}
	}

	//插入数据操作(增)
	@Test
	public void testInsert()
	{
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties pr = new Properties();
			pr.load(is);
			
			String user = pr.getProperty("user");
			String password = pr.getProperty("password");
			String url = pr.getProperty("url");
			String driverClass = pr.getProperty("driverClass");
			
			Class.forName(driverClass);
			
			conn = (Connection) DriverManager.getConnection(url,user,password);
			
			//4.预编译sql语句：返回PreparedStatement的实例
			String sql = "insert into customers(name,email,birth)values(?,?,?)";
			ps = (PreparedStatement) conn.prepareStatement(sql);
			
			//5.填充占位符
			ps.setString(1, "石文学");
			ps.setString(2, "swx@gmail.com");
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
			java.util.Date date = simpleDateFormat.parse("2002-02-07");
			ps.setDate(3, new Date(date.getDate()));
			
			//6.执行操作
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {	
			//7.关闭资源
			if(ps!=null)
			{
				try {
					ps.cancel();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!=null)
			{	
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
	}
	
	//修改customers表的一条记录
	@Test
	public void testUpdate()
	{
		//1.获取数据库的连接
		Connection conn = null;
		java.sql.PreparedStatement ps = null;
		try {
			conn = JDBCUtils.getConnection();
			//2.预编译sql语句，返回perparedstatement的实例
			String sql = "update customers set name = ? where id = ?";
			ps = conn.prepareStatement(sql);
			//3.填充占位符
			ps.setObject(1, "莫扎特");
			ps.setObject(2, 18);
			//4.执行
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			//5.关闭资源
			JDBCUtils.closeResource(conn, ps);
		}
	}
	
	//删除customers表中的一条记录
	@Test
	public void testDelete()
	{
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = JDBCUtils.getConnection();
			String sql = "delete from customers where id = ?";
			ps = (PreparedStatement) conn.prepareStatement(sql);
			ps.setObject(1, 1);
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtils.closeResource(conn, ps);
		}
	}

}
